﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using Lswweb.Common;

namespace Lswweb.SqlServerDAL
{
    public delegate T ReaderBind<T>(Hashtable reader);
    public delegate IDataParameter[] ParametersBuilder<T>(T t);

    public class Builder<T>
    {
        public event ReaderBind<T> CreateModel;
        public event ParametersBuilder<T> GetParameters;

        protected string tableName;
        protected string identity;
        protected string identityParameterName;
        protected string orderBy;

        public Builder(string tableName,string identity)
        {
            this.tableName = tableName;
            this.identity = identity;
            this.identityParameterName = DataAccess.AdoHelper().BuildParameterName(identity);
        }

        #region 查询记录

        public T GetModel(string where, string orderBy, params IDataParameter[] parameters)
        {
            return this.GetModel(this.tableName, string.Empty, where, orderBy, parameters);
        }
        /// <summary>
        /// 根据条件语句取得实体类，如果结果为多条记录则返回第一条记录的实体类。
        /// </summary>
        /// <param name="fields">要查询的字段，为string.Empty则查询所有字段</param>
        /// <param name="where">条件语句</param>
        /// <param name="parameters">参数列表</param>
        /// <returns>取得记录则返回实休类，无记录则返回null</returns>
        public T GetModel(string tableName,string fields,string where,string orderBy,params IDataParameter[] parameters)
        {
            if (string.IsNullOrEmpty(fields))
                fields = "*";

            if (string.IsNullOrEmpty(tableName))
                tableName = this.tableName;

            string sql = "select top 1 " + fields + " from " + tableName;
            if (!string.IsNullOrEmpty(where))
                sql += " where " + where;

            if (!string.IsNullOrEmpty(orderBy))
                sql += " order by " + orderBy;

            T model = default(T);
            using (IDataReader reader = DataAccess.ExecuteReader(sql, parameters))
            {
                if (reader.Read())
                    model = GetModel(reader);
            }
            return model;
        }
        public T GetModel(string fields, object id)
        {
            IDataParameter parameter = DataAccess.CreateParameter(identityParameterName, id);
            string where = identity + "=" + identityParameterName;
            return GetModel(string.Empty,fields,where,string.Empty,parameter);
        }
        public T GetModel(object id)
        {
            return GetModel(string.Empty,id);
        }
        public IList<T> GetModels(string tableName,string declare,string fileds,string where,string orderBy,int pageIndex,int pageSize,out int totalRecord,params IDataParameter[] parameters)
        {
            if (string.IsNullOrEmpty(orderBy))
                orderBy = string.Format("order by {0} desc",this.identity);

            IList<T> list = new List<T>();
            using (IDataReader reader = DataAccess.GetDataReader(tableName,declare,fileds, where, orderBy, pageIndex, pageSize,out totalRecord, parameters))
            {
                while (reader.Read())
                    list.Add(GetModel(reader));
            }
            return list;
        }
        public IList<T> GetModels(string fileds, string where, string orderBy, int pageIndex, int pageSize, out int totalRecord, params IDataParameter[] parameters)
        {
            return GetModels(tableName, string.Empty, fileds, where, orderBy, pageIndex, pageSize, out totalRecord, parameters);
        }
        public IList<T> GetModels(string sql, params IDataParameter[] parameters)
        {
            IList<T> list = new List<T>();
            using (IDataReader reader = DataAccess.ExecuteReader(sql, parameters))
            {
                while (reader.Read())
                    list.Add(GetModel(reader));
            }
            return list;
        }
        public IList<T> GetModels()
        {
            int total;
            return GetModels(string.Empty, string.Empty, orderBy, 1, 9999,out total, null);
        }

        #endregion

        #region 添加记录

        /// <summary>
        /// 添加记录
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="t"></param>
        /// <returns>添加失败返回0，正常返回ExecuteScalar的值，如无ExecuteScalar值或不为int型返回-1</returns>
        public int Insert(string sql, T t)
        {
            if (t == null)
                return 0;

            return DataAccess.ExecuteScalar(
                sql,
                this.GetParametersByModel(t)).As<int>();
        }

        /// <summary>
        /// 多个字段之间用“,”隔开
        /// </summary>
        /// <param name="fileds"></param>
        /// <param name="t"></param>
        /// <returns></returns>
        public int Insert(T t,params string[] fileds)
        {
            if (fileds.Length==0)
                throw new NotImplementedException("必须有添加的记录字段");

            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.Append("insert into ");
            sqlBuilder.Append(tableName);
            sqlBuilder.Append("(");
            for (int i = 0; i < fileds.Length; i++)
            {
                sqlBuilder.Append(fileds[i]);
                if (i < (fileds.Length - 1))
                    sqlBuilder.Append(",");
            }
            sqlBuilder.Append(") values (");
            for (int i = 0; i < fileds.Length; i++)
            {
                sqlBuilder.Append("@");
                sqlBuilder.Append(fileds[i].TrimEnd(']').TrimStart('['));
                if (i < (fileds.Length - 1))
                    sqlBuilder.Append(",");
            }
            sqlBuilder.Append(");select @@IDENTITY;");

            return this.Insert(sqlBuilder.ToString(), t);
        }

        #endregion

        #region 更新记录

        /// <summary>
        /// 根据实体类和sql语然更新记录
        /// </summary>
        /// <param name="sql">更新记录的sql语句</param>
        /// <param name="t">实体类</param>
        /// <returns>更新成功返回true，失败返回false</returns>
        public bool Update(string sql,T t)
        {
            if (t == null)
                return false;

            IDataParameter[] parameters = GetParametersByModel(t);
            return Convert.ToBoolean(DataAccess.ExecuteNonQuery(sql,parameters));
        }

        public bool Update(T t,string where ,params string[] fileds)
        {
            StringBuilder sqlBuilder = new StringBuilder();
            sqlBuilder.Append("update ");
            sqlBuilder.Append(tableName);
            sqlBuilder.Append(" set ");
            for (int i = 0; i < fileds.Length; i++)
            {
                sqlBuilder.Append(fileds[i]);
                sqlBuilder.Append("=");
                sqlBuilder.Append(DataAccess.AdoHelper().BuildParameterName(fileds[i]));

                if (i == fileds.Length - 1)
                    break;

                sqlBuilder.Append(",");
            }

            if (!string.IsNullOrEmpty(where))
            {
                sqlBuilder.Append(" where ");
                sqlBuilder.Append(where);
            }

            return Update(sqlBuilder.ToString(),t);
        }

        /// <summary>
        /// 根据条件语句更新记录某字段的值，字段的新值参数为 @ + fileName
        /// </summary>
        /// <param name="fieldNames">要更新的字段名，多个字段之间用“,”隔开。</param>
        /// <param name="where">条件语句</param>
        /// <param name="parameters">sql参数，至少要一个参数，即字段新值的参数。</param>
        /// <returns>更新成功返回true，失败返回false</returns>
        public bool UpdateFields(string fieldNames,string where,params IDataParameter[] parameters)
        {
            return DataAccess.UpdateFields(tableName, fieldNames, where, parameters);
        }
        /// <summary>
        /// 根据主键Id更新记录的某字段值，字段的新值参数为 @ + fileName
        /// </summary>
        /// <param name="fieldNames">要更新的字段名，多个字段之间用“,”隔开。</param>
        /// <param name="id">主键Id</param>
        /// <param name="fieldValues">字段新值集合，它的个数要与fieldNames中的字段个数相同，并且顺序要相同。</param>
        /// <returns>更新成功返回true，失败返回false</returns>
        public bool UpdateFields(string fieldNames,object id,params object[] fieldValues)
        {
            string[] list=fieldNames.Split(',');
            IDataParameter[] parameters = new IDataParameter[list.Length + 1];
            parameters[0]=DataAccess.CreateParameter(identityParameterName, id);

            for (int i = 0; i < list.Length; i++)
            {
                object obj = (fieldValues == null) ? null : fieldValues[i];
                if(obj==null || obj==DBNull.Value)
                    parameters[i + 1] = DataAccess.CreateParameter(list[i], DBNull.Value);
                else
                    parameters[i + 1] = DataAccess.CreateParameter(list[i], obj);
            }
                
            string where = identity + "=" + identityParameterName;
            try
            {
                return UpdateFields(fieldNames, where, parameters);
            }
            catch
            {
                return false;
            }
        }

        #endregion

        #region 删除记录

        public bool Delete(object id)
        {
            IDataParameter parameter = DataAccess.CreateParameter(identityParameterName, id);
            string where = identity + "=" + identityParameterName;

            return Delete(where,parameter);
        }
        public bool Delete(string where, params IDataParameter[] parameters)
        {
            string sql = "delete from " + tableName;
            if (!string.IsNullOrEmpty(where))
                sql += " where " + where;

            return Convert.ToBoolean(DataAccess.ExecuteNonQuery(sql, parameters));
        }

        #endregion

        #region other

        /// <summary>
        /// 根据条件判断记录是否存在
        /// </summary>
        /// <param name="where">条件语句</param>
        /// <param name="paraeters">sql参数</param>
        /// <returns>记录存在返回true,记录不存在返回false</returns>
        public bool IsHas(string where,params IDataParameter[] paraeters)
        {
            return Convert.ToBoolean(DataAccess.GetRecord(tableName,where,paraeters));
        }
        /// <summary>
        /// 根据一个字段的值判断记录是否存在。
        /// </summary>
        /// <param name="fieldName">字段名称</param>
        /// <param name="fieldValue">字段的值</param>
        /// <returns>记录存在返回true,记录不存在返回false</returns>
        public bool IsHas(string fieldName,object fieldValue)
        {
            if (fieldName == identity)
                return IsHas(fieldValue);
            else
                return IsHas(0, fieldName, fieldValue);
        }
        /// <summary>
        /// 根据主键和一个字段判断记录是否存在 公式：Id<>@Id and fieldName=@FieldName
        /// </summary>
        /// <param name="id">主键Id</param>
        /// <param name="fieldName">字段名称</param>
        /// <param name="fieldValue">>字段的值</param>
        /// <returns>记录存在返回true,记录不存在返回false</returns>
        public bool IsHas(object id,string fieldName,object fieldValue)
        {
            if (string.IsNullOrEmpty(fieldName) || fieldValue == null)
                return false;
            IDataParameter[] parameters = { DataAccess.CreateParameter(fieldName, fieldValue),
                                            DataAccess.CreateParameter(identity,id)};
            string where = fieldName + "=" + parameters[0].ParameterName + " and " + identity + " <> " + parameters[1].ParameterName;

            return IsHas(where, parameters);
        }
        public bool IsHas(object id)
        {
            IDataParameter parameter = DataAccess.CreateParameter(identity, id);
            string where = identity + "=" + parameter.ParameterName;

            return IsHas(where, parameter);
        }

        /// <summary>
        /// 根据条件取得某字段的值
        /// </summary>
        /// <param name="fieldName">要取值的字段名称</param>
        /// <param name="where">条件语句</param>
        /// <param name="parameters">sql参数</param>
        /// <returns>字段值</returns>
        public object GetFieldValue(string fieldName, string where, params IDataParameter[] parameters)
        {
            return DataAccess.GetFieldValue(tableName,fieldName,where,parameters);
        }
        /// <summary>
        /// 根据主键Id取得某字段的值
        /// </summary>
        /// <param name="fieldName">要取值的字段名称</param>
        /// <param name="id">主键Id</param>
        /// <returns>字段值</returns>
        public object GetFieldValue(string fieldName, object id)
        {
            IDataParameter parameter = DataAccess.CreateParameter(identityParameterName, id);
            string where = identity + "=" + identityParameterName;
            return GetFieldValue(fieldName, where, parameter);
        }

        public IList<object> GetFieldValue(string fieldName, string where,string orderBy, params IDataParameter[] parameters)
        {
            if (string.IsNullOrEmpty(fieldName))
                throw new Exception("字段fieldName不能为空。");
            string sql = "select " + fieldName + " from " + tableName;
            if (!string.IsNullOrEmpty(where))
                sql += " where " + where;
            if (!string.IsNullOrEmpty(orderBy))
                sql += " order by " + orderBy;

            IList<object> list = new List<object>();
            using(IDataReader reader=DataAccess.ExecuteReader(sql,parameters))
            {
                while (reader.Read())
                    list.Add(reader[fieldName]);

                reader.Close();
            }

            return list;
        }
        #endregion

        #region public properties

        public string TableName
        {
            get { return this.tableName; }
        }

        #endregion

        #region Protected Methods

        public T GetModel(IDataReader reader)
        {
            if (CreateModel != null)
            {
                Hashtable ht = new Hashtable();
                for (int i = 0; i < reader.FieldCount; i++)
                {
                    if (!reader.IsDBNull(i))
                        ht.Add(reader.GetName(i), reader.GetValue(i));
                }

                return CreateModel(ht);
            }
            else
                throw new Exception("必须配置CreateModel方法。");
        }

        protected IDataParameter[] GetParametersByModel(T t)
        {
            if (GetParameters != null)
                return GetParameters(t);
            else
                throw new Exception("必须配置GetParameters方法。");
        }

        #endregion
    }
}
